"""
:param 清洗data/stu_data下的excel表格，以json格式保存到data下
"""
from pandas.core.frame import DataFrame
# filter_file_path按时间顺序返回所有excel表格相对路径
from filter_file_path import filter_file_path
import pandas as pd
import re
import json


def filter_stu_location():
    # 一日数据
    # 清洗学生所在省份的状况(8月31号)，字典格式保存到stu_location.json文件里
    # 保存的格式如[{'江苏省':100},{'浙江省':20}]
    # 省份按字典序排列
    res = dict()
    df = pd.read_excel('./data/stu_data/2020-8/2020-8-31.xlsx')
    for i in df["你目前所在城市"]:
        # 正则取出省份
        tmp = re.search(r'.*?/', i)
        tmp = tmp.group(0)[:-1]
        # dict统计省出现次数
        res[tmp] = res[tmp] + 1 if tmp in res else 1

    with open('data/stu_data/stu_location.json', 'w') as f:
        json.dump(res, f)


def filter_all_grade_history_data():
    # 多日数据
    # 各个年级每天的打卡总人数分别保存到以下json文件里
    # stu_2019.json, stu_2018.json, stu_2017.json
    # 保存到格式如[{'2020-7-30':1000}]
    files = filter_file_path()
    stu2017 = {}
    stu2018 = {}
    stu2019 = {}
    for i in files:
        df = pd.read_excel(i)
        # 2020-7月还没有2020届, 剔除异常数据
        df = df.drop(df.loc[df["年级"] == 2020].index)
        # 获取日期
        key = re.search(r'\d*-\d*-\d*', i)
        key = key.group(0)
        for j in df["年级"]:
            if j == 2017:
                stu2017[key] = 1 if key not in stu2017 else stu2017[key] + 1
            if j == 2018:
                stu2018[key] = 1 if key not in stu2018 else stu2018[key] + 1
            if j == 2019:
                stu2019[key] = 1 if key not in stu2019 else stu2019[key] + 1
        # 写入三个文件
    for file, data in [('data/stu_data/stu_2017.json', stu2017), ('data/stu_data/stu_2018.json', stu2018), ('data/stu_data/stu_2019.json', stu2019)]:
        with open(file, 'w') as f:
            json.dump(data, f)
    all_grade_count = {'2017': stu2017["2020-8-31"], '2018': stu2018["2020-8-31"], '2019': stu2019["2020-8-31"]}
    with open('data/stu_data/stu_data.json', 'w') as f:
        json.dump(all_grade_count, f)


def filter_student_data():
    # 一日数据
    # 学生打卡人数
    df = pd.read_excel('./data/stu_data/2020-8/2020-8-31.xlsx')
    df = df.drop(df.loc[df["年级"] == 2020].index)
    res = {"打卡人数": len(df)}
    with open('data/stu_data/stu_data.json', 'w') as f:
        json.dump(res, f)


def filter_sign_after_twenty():
    # 一日数据
    # 统计各年级20点后打卡人数，保存到json文件里，[{2019:xxx}, {2018:xxx}, {2017:xxx}]
    stu = dict()
    df = pd.read_excel('./data/stu_data/2020-8/2020-8-31.xlsx')
    df = df.drop(df.loc[df["年级"] == 2020].index)
    for j in df.index:
        if df.loc[j]["年级"] == 2017:
            # 在第一天20点后到第二天9点前的数据
            if int(df.loc[j]["提交时间"][11:13]) >= 20 or int(df.loc[j]["提交时间"][11:13]) < 9:
                stu[2017] = 1 if 2017 not in stu else stu[2017] + 1
        if df.loc[j]["年级"] == 2018:
            if int(df.loc[j]["提交时间"][11:13]) >= 20 or int(df.loc[j]["提交时间"][11:13]) < 9:
                stu[2018] = 1 if 2018 not in stu else stu[2018] + 1
        if df.loc[j]["年级"] == 2019:
            if int(df.loc[j]["提交时间"][11:13]) >= 20 or int(df.loc[j]["提交时间"][11:13]) < 9:
                stu[2019] = 1 if 2019 not in stu else stu[2019] + 1
    with open("data/stu_data/stu_after_twenty.json", "w") as f:
        json.dump(stu, f)


def filter_all_history_data():
    # 多日数据
    # 每天的打卡总人数分别保存到以下json文件里
    # 保存到格式如[{'2020-7-30':1000}]
    files = filter_file_path()
    stu = {}
    for i in files:
        df = pd.read_excel(i)
        # 2020-7月还没有2020届, 剔除异常数据
        df = df.drop(df.loc[df["年级"] == 2020].index)
        # 获取日期
        key = re.search(r'\d*-\d*-\d*', i)
        key = key.group(0)
        for j in df["年级"]:
            stu[key] = 1 if not key in stu else stu[key] + 1
        # 写入三个文件
    with open('data/stu_data/stu_all_history.json', 'w') as f:
        json.dump(stu, f)


def run():
    filter_sign_after_twenty()
    filter_student_data()
    filter_all_grade_history_data()
    filter_stu_location()
    filter_all_history_data()


if __name__ == '__main__':
    run()
